<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Connection_model extends CI_Model {


	public function updateStudent($dataArray){

		$sql  = "UPDATE student SET ";
		$sql .= "last_name=?,";
		$sql .= "first_name=?,";
		$sql .= "middle_name=?,";
		$sql .= "address=?,";
		$sql .= "age=?,";
		$sql .= "birthday=?,";
		$sql .= "year_level=?,";
		$sql .= "school_year=?,";
		$sql .= "gender=?,";
		$sql .= "image_path=?,";
		$sql .= "last_updated=? ";
		$sql .= "WHERE student_id=?";

		$dateTime = new DateTime();
		$dateString = $dateTime->format('Y-m-d H:i:s');

		// $sql  = "UPDATE student SET ";
		// $sql .= "last_name='".$dataArray['last_name']."',";
		// $sql .= "first_name='".$dataArray['first_name']."',";
		// $sql .= "middle_name='".$dataArray['middle_name']."',";
		// $sql .= "address='".$dataArray['address']."',";
		// $sql .= "age='".$dataArray['age']."',";
		// $sql .= "birthday='".$dataArray['birthday']."',";
		// $sql .= "year_level='".$dataArray['year_level']."',";
		// $sql .= "school_year='".$dataArray['school_year']."',";
		// $sql .= "gender='".$dataArray['gender']."',";
		// $sql .= "image_path='".$dataArray['image_path']."',";
		// $sql .= "last_updated='".$dateString."' ";
		// $sql .= "WHERE student_id='".$dataArray['student_id']."'";
		
		
		$query = $this->db->query($sql, array(
			$dataArray['last_name'],
			$dataArray['first_name'],
			$dataArray['middle_name'],
			$dataArray['address'],
			$dataArray['age'],
			$dataArray['birthday'],
			$dataArray['year_level'],
			$dataArray['school_year'],
			$dataArray['gender'],
			$dataArray['image_path'],
			$dateString,
			$dataArray['student_id']
		));

		$ret = false;
		if($query){
			$sql2 = "SELECT * FROM student WHERE student_id=?";
			$query2 = $this->db->query($sql2,array($dataArray['student_id']));
			$ret =$query2->result_array();
		}
		//return $sql;
		return $ret;
	}

	public function updateGuardian($dataArray){

		$sql  = "UPDATE guardian SET ";
		$sql .= "last_name=?,";
		$sql .= "first_name=?,";
		$sql .= "middle_name=?,";
		$sql .= "contact_number=?,";
		$sql .= "address=?,";
		$sql .= "age=?,";
		$sql .= "birthday=?,";
		$sql .= "gender=?,";
		$sql .= "last_updated=? ";
		$sql .= "WHERE guardian_id=?";

		$dateTime = new DateTime();
		$dateString = $dateTime->format('Y-m-d H:i:s');

		// $sql  = "UPDATE guardian SET ";
		// $sql .= "last_name='".$dataArray['last_name']."',";
		// $sql .= "first_name='".$dataArray['first_name']."',";
		// $sql .= "middle_name='".$dataArray['middle_name']."',";
		// $sql .= "contact_number='".$dataArray['contact_number']."',";
		// $sql .= "address='".$dataArray['address']."',";
		// $sql .= "age='".$dataArray['age']."',";
		// $sql .= "birthday='".$dataArray['birthday']."',";
		// $sql .= "gender='".$dataArray['gender']."',";
		// $sql .= "last_updated='".$dateString."' ";
		// $sql .= "WHERE guardian_id='".$dataArray['guardian_id']."'";
		
		
		$query = $this->db->query($sql, array(
			$dataArray['last_name'],
			$dataArray['first_name'],
			$dataArray['middle_name'],
			$dataArray['contact_number'],
			$dataArray['address'],
			$dataArray['age'],
			$dataArray['birthday'],
			$dataArray['gender'],
			$dateString,
			$dataArray['guardian_id']
		));

		$ret = false;
		if($query){
			$sql2 = "SELECT * FROM guardian WHERE guardian_id=?";
			$query2 = $this->db->query($sql2,array($dataArray['guardian_id']));
			$ret =$query2->result_array();
		}
		// return $sql;
		return $ret;
	}
	
	public function searchStudentByID($id=-1){
		$cleanedID = $this->db->escape_str($id);
		$sql  = "SELECT * ";
		$sql .= "FROM student ";
		$sql .= "WHERE student_id='".$cleanedID."'";
		$query = $this->db->query($sql);
		//$query = $this->db->query($sql,array($val,$val,$val));
		//return $sql;
		return $query->result_array();		
	}

	public function addGuardianAccount($accountArray,$guardianArray){

		$accountArray['password'] = md5($accountArray['password']);
		$query = $this->db->insert('account', $accountArray);
		if($query){
			$guardianArray['account_id'] = $this->db->insert_id();

			$dateTime = new DateTime();
			$dateString = $dateTime->format('Y-m-d H:i:s');
			
			$guardianArray['last_updated'] = $dateString;
			$query2 = $this->db->insert('guardian', $guardianArray);
			return $query2;
		}
		

		//return $sql;
		return $query;
	}

	public function addStudent($studentArray){
	
		$query = $this->db->insert('student', $studentArray);
		$last_id = $this->db->insert_id();
		if($query){
			$data = $this->searchStudentByID($last_id);
			
			if($data!=null){
				$student = $data['0'];
				$student['image_path'] = "https://chart.googleapis.com/chart?cht=qr&chs=300x300&choe=UTF-8&chl=".$last_id;
				$query = $this->updateStudent($student);
			}
		}
		//return $sql;
		return $query;
	}
	public function deleteStudent($id){

		$sql = "DELETE FROM student WHERE student_id=?";
		$query = $this->db->query($sql,array($id));

		//return $sql;
		return $query;
	}

	public function deleteGuardian($id){

		//delete all students tagged
		$sql = "DELETE FROM student WHERE guardian_id=?";
		$query = $this->db->query($sql,array($id));

		//get the account id using guardian id
		$acctId = -1;
		$sql = "SELECT account_id FROM guardian WHERE guardian_id=? LIMIT 1" ;
		$query = $this->db->query($sql,array($id));
		$data = $query->result_array();
		if($data[0]['account_id']!=null){
			$acctId = $data[0]['account_id'];
		}
		

		$sql = "DELETE FROM guardian WHERE guardian_id=?";
		$query = $this->db->query($sql,array($id));

		if($acctId!=null){
			if($acctId>0){
				//Delete the account
				$sql = "DELETE FROM account WHERE account_id=?";
				$query = $this->db->query($sql,array($acctId));

				if($this->session->userdata('account_id') != null){
					if($this->session->userdata('account_id') == $acctId){

						$sessionData = array(
							'account_id'=> '',
							'username'=> '',
							'userlevel'=> ''
						);
						$this->session->unset_userdata($sessionData);				
					}
				}
			}
		}
		
		
		return $query;
	}
	
	public function api_redirect($token,$mobileNumber){
		if($this->has_token($mobileNumber)){
			$data = array(
			'access_token'=>$token
			);
			$this->db->where('subscriber_number', $mobileNumber);
			$this->db->update('api_access' ,$data);
		}else{
			$data = array(
			'access_token'=>$token,
			'subscriber_number'=>$mobileNumber
			);
			$query = $this->db->insert('api_access', $data );
		}
		
	}
	
	private function has_token($mobileNumber)
	{
		
		$sql = "SELECT * FROM api_access WHERE subscriber_number =?";
		$query = $this->db->query($sql,$mobileNumber);
		
		if($query->num_rows()>=1){
			return true;
		}else{
			return false;
		}
		
	}
	
	
	
	
}